package com.snail.app.gather.service;

import com.snail.app.DbOperationUtil;
import com.snail.app.config.entity.DbConfigDatabase;
import com.snail.app.config.service.DbConfigDatabaseService;
import com.snail.app.gather.entity.DataSyncTriggerInfo;
import com.snail.common.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * @description
 * @date 2021/12/9
 * @autor houyanfeng
 */
@Slf4j
@Service
@Transactional
public class RawDataService {
    @Autowired
    private DbConfigDatabaseService dbConfigDatabaseService;
    @Autowired
    private DbOperationUtil dbOperationUtil;

    /**
     * @param params Map<String,Object>传参：<br/>
     *               startTime 开始时间 yyyy-MM-dd；<br/>
     *               endTime 结束时间 yyyy-MM-dd；<br/>
     *               batch 数量
     * @param dataSyncTrigger 数据同步触发条件类
     * @return List  Map的List集合
     * @throws Exception
     */
    public List<Map<String,Object>> listChangeRawData(Map<String,Object> params, DataSyncTriggerInfo dataSyncTrigger) throws Exception{
        String configDBId = dataSyncTrigger.getGatherDbId();
        DbConfigDatabase dbConfigDatabase = dbConfigDatabaseService.getById(configDBId);
        String sql = "";
        if("oracle".equals(dbConfigDatabase.getType())){
            sql = createSqlOracle(params, dataSyncTrigger);
        }else if("sqlserver".equals(dbConfigDatabase.getType())){
            sql = createSqlServer(params, dataSyncTrigger);
        }else if("mysql".equals(dbConfigDatabase.getType())) {
            sql = createSqlMysql(params, dataSyncTrigger);
        }
        System.out.println(dataSyncTrigger.getId()+" " + " 数据sql = " + sql);
        List<Map<String, Object>> dataMaps = dbOperationUtil.queryListMap(dataSyncTrigger.getGatherDbId(), sql);
        return dataMaps;
    }

    private String createSqlOracle(Map<String,Object> params, DataSyncTriggerInfo dataSyncTrigger){
        StringBuilder sql = new StringBuilder("select ");
        //根据更新时间判断数据是否发生变化
        if ("updatetime".equals(dataSyncTrigger.getMode().toLowerCase())) {
            //初始化时data字段可能为空，手动设为'1900-01-01'以支持代码运行
            if (StringUtil.isEmpty(dataSyncTrigger.getData())) {
                dataSyncTrigger.setData("1900-01-01 00:00:01");
            }
            if(!StringUtil.isEmpty(dataSyncTrigger.getQueryField())){
                sql.append(" " + dataSyncTrigger.getQueryField());
                if(!sql.toString().contains(dataSyncTrigger.getField().toLowerCase())){
                    if(dataSyncTrigger.getField().contains(".")){
                        sql.append(", "+dataSyncTrigger.getField().toLowerCase());
                    }else{
                        sql.append(", tar."+dataSyncTrigger.getField().toLowerCase());
                    }
                }
            }else {
                sql.append(" tar.* ");
            }
            if(!sql.toString().contains(dataSyncTrigger.getField())){
                sql.append(","+dataSyncTrigger.getField());
            }
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            //关联语句
            if(!StringUtil.isEmpty(dataSyncTrigger.getJoinSql())){ sql.append(" "+ dataSyncTrigger.getJoinSql()); }
            sql.append(" where 1=1");
            if (!StringUtil.isEmpty(params.get("startTime")) || !StringUtil.isEmpty(params.get("endTime"))) {
                if (!StringUtil.isEmpty(params.get("startTime"))) {
                    if(dataSyncTrigger.getField().contains(".")){
                        sql.append(" and "+dataSyncTrigger.getField());
                    }else{ sql.append(" and tar."+dataSyncTrigger.getField()); }
                    sql.append(" > to_date('" + params.get("startTime")).append("','yyyy-mm-dd hh24:mi:ss') ");
                }
                if (!StringUtil.isEmpty(params.get("endTime"))) {
                    if(dataSyncTrigger.getField().contains(".")){
                        sql.append(" and "+dataSyncTrigger.getField());
                    }else{ sql.append(" and tar."+dataSyncTrigger.getField());
                    }
                    sql.append(" < to_date('" + params.get("endTime")).append("','yyyy-mm-dd hh24:mi:ss')");
                }
            } else {
                if(dataSyncTrigger.getField().contains(".")){
                    sql.append(" and "+dataSyncTrigger.getField());
                }else{
                    sql.append(" and tar."+dataSyncTrigger.getField());
                }
                sql.append(" >= to_date('" + dataSyncTrigger.getData() + "','yyyy-mm-dd hh24:mi:ss')");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" and (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            if (!StringUtil.isEmpty(params.get("batch"))) { sql.append(" and rownum <= ").append(params.get("batch")); }
            if(dataSyncTrigger.getField().contains(".")) {
                sql.append(" order by " + dataSyncTrigger.getField());
            }else{
                sql.append(" order by tar." + dataSyncTrigger.getField());
            }
        }else if ("full".equals(dataSyncTrigger.getMode().toLowerCase())) {//全量同步
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" where (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            if (!StringUtil.isEmpty(params.get("batch"))) { sql.append(" and rownum <= ").append(params.get("batch")); }
            if(!StringUtil.isEmpty(dataSyncTrigger.getField())){
                if(dataSyncTrigger.getField().contains(".")) {
                    sql.append(" order by " + dataSyncTrigger.getField());
                }else{
                    sql.append(" order by tar." + dataSyncTrigger.getField());
                }
            }
        }
        return sql.toString().replace(" 1=1 and","");
    }

    private String createSqlServer(Map<String,Object> params, DataSyncTriggerInfo dataSyncTrigger){
        StringBuilder sql = new StringBuilder("select  ");
        //根据更新时间判断数据是否发生变化
        if ("updatetime".equals(dataSyncTrigger.getMode().toLowerCase())) {
            //初始化时data字段可能为空，手动设为'1900-01-01'以支持代码运行
            if (StringUtil.isEmpty(dataSyncTrigger.getData())) {
                dataSyncTrigger.setData("1900-01-01 00:01");
            }
            if (!StringUtil.isEmpty(params.get("batch"))) { sql.append(" top ").append(params.get("batch")); }
            if(!StringUtil.isEmpty(dataSyncTrigger.getQueryField())){
                sql.append(" " + dataSyncTrigger.getQueryField());
            }else {
                sql.append(" tar.* ");
            }
            if(!sql.toString().contains(dataSyncTrigger.getField().toLowerCase())){
                if(dataSyncTrigger.getField().contains(".")){
                    sql.append(", "+dataSyncTrigger.getField().toLowerCase());
                }else{
                    sql.append(", tar."+dataSyncTrigger.getField().toLowerCase());
                }
            }
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            //关联语句
            if(!StringUtil.isEmpty(dataSyncTrigger.getJoinSql())){ sql.append(" "+ dataSyncTrigger.getJoinSql()); }
            sql.append(" where 1=1 ");
            if (!StringUtil.isEmpty(params.get("startTime")) || !StringUtil.isEmpty(params.get("endTime"))) {
                if (!StringUtil.isEmpty(params.get("startTime"))) {
                    sql.append(" and tar."+dataSyncTrigger.getField() + " >= '" + params.get("startTime")).append("'");
                }
                if (!StringUtil.isEmpty(params.get("endTime"))) {
                    sql.append(" and tar."+dataSyncTrigger.getField() + " < '" + params.get("endTime")).append("'");
                }
            } else {
                sql.append(" and tar."+dataSyncTrigger.getField() + " > '" + dataSyncTrigger.getData() + "'");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" and (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            sql.append(" order by tar." + dataSyncTrigger.getField());
        }else if ("full".equals(dataSyncTrigger.getMode().toLowerCase())) {//全量同步
            if (!StringUtil.isEmpty(params.get("batch"))) {
                sql.append(" top ").append(params.get("batch"));
            }
            sql.append(" * ");
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" where (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            if(!StringUtil.isEmpty(dataSyncTrigger.getField())){
                sql.append(" order by tar." + dataSyncTrigger.getField());
            }
        }
        return sql.toString();
    }

    private String createSqlMysql(Map<String,Object> params, DataSyncTriggerInfo dataSyncTrigger){
        StringBuilder sql = new StringBuilder("select  ");
        //根据更新时间判断数据是否发生变化
        if ("updatetime".equals(dataSyncTrigger.getMode().toLowerCase())) {
            //初始化时data字段可能为空，手动设为'1900-01-01'以支持代码运行
            if (StringUtil.isEmpty(dataSyncTrigger.getData())) {
                dataSyncTrigger.setData("1900-01-01 00:01");
            }
            if(!StringUtil.isEmpty(dataSyncTrigger.getQueryField())){
                sql.append(" " + dataSyncTrigger.getQueryField());
            }else {
                sql.append(" tar.* ");
            }
            if(!sql.toString().contains(dataSyncTrigger.getField().toLowerCase())){
                if(dataSyncTrigger.getField().contains(".")){
                    sql.append(", "+dataSyncTrigger.getField().toLowerCase());
                }else{
                    sql.append(", tar."+dataSyncTrigger.getField().toLowerCase());
                }
            }
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            //关联语句
            if(!StringUtil.isEmpty(dataSyncTrigger.getJoinSql())){ sql.append(" "+ dataSyncTrigger.getJoinSql()); }
            sql.append(" where 1=1 ");
            if (!StringUtil.isEmpty(params.get("startTime")) || !StringUtil.isEmpty(params.get("endTime"))) {
                if (!StringUtil.isEmpty(params.get("startTime"))) {
                    sql.append(" and tar."+dataSyncTrigger.getField() + " >= '" + params.get("startTime")).append("'");
                }
                if (!StringUtil.isEmpty(params.get("endTime"))) {
                    sql.append(" and tar."+dataSyncTrigger.getField() + " < '" + params.get("endTime")).append("'");
                }
            } else {
                sql.append(" and tar."+dataSyncTrigger.getField() + " > '" + dataSyncTrigger.getData() + "'");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" and (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            sql.append(" order by tar." + dataSyncTrigger.getField());
            if (!StringUtil.isEmpty(params.get("batch"))) { sql.append(" limit ").append(params.get("batch")); }
        }else if ("full".equals(dataSyncTrigger.getMode().toLowerCase())) {//全量同步
            if (!StringUtil.isEmpty(params.get("batch"))) {
                sql.append(" top ").append(params.get("batch"));
            }
            sql.append(" * ");
            if(StringUtil.isEmpty(dataSyncTrigger.getFromTableName())){
                sql.append(" from "+dataSyncTrigger.getTableName()+" tar");
            }else{
                sql.append(" from "+dataSyncTrigger.getFromTableName()+" tar");
            }
            if (!StringUtil.isEmpty(dataSyncTrigger.getSqlConditions())) {
                sql.append(" where (" + dataSyncTrigger.getSqlConditions() + ")");
            }
            if(!StringUtil.isEmpty(dataSyncTrigger.getField())){
                sql.append(" order by tar." + dataSyncTrigger.getField());
            }
            if (!StringUtil.isEmpty(params.get("batch"))) { sql.append(" limit ").append(params.get("batch")); }
        }
        return sql.toString();
    }
}
